<?

/** Generic SQL classes for basic select, update, delete, insert and 
 *  create (new table). Not to be used when sub-selects or other more
 *  complex SQL queries are to be run.
 *
 *  Using these classes, however, makes the code much more readable,
 *  and hence, maintainable and extendable. Not to mention debugable...
 *
 *  They do not actually connect to the database, but they return
 *  string that can be run on one.
 *
 *  Important fact is, that some databases requires string single quoted
 *  (PostgreSQL) and some others double quoted (MySql). This library
 *  takes care of that.
 *
 *  This collection of functions (libary) is licensed under GPL2.
 *  See LICENSE or www.gnu.org for more details.
 *  @author Jyry Kuukkanen
 *  @copyright Jyry Kuukkanen
 *
 *  $Id: sodax.php,v 1.1 2005/03/13 17:01:53 jyry Exp $
 */

include_once('sobaseclass.php');
include_once('DB.php');
include_once('sogeneric.php');
include_once('sodebug.php');


/* Datatype constants */
define(SODT_NUM, 'num');
define(SODT_COL, 'col');
define(SODT_DATE, 'date');
define(SODT_TIME, 'time');
define(SODT_DATETIME, 'datetime');
define(SODT_TEXT, 'text');
define(SODT_BOOL, 'bool');


/** Class to generate SQL clauses for update+insert+delete+select
 *  This class provides basic functions to create SQL clauses for select,
 *  update, insert and delete. It is backend independent and handles
 *  issues with datatypes, like quoting strings (single or double).
 *
 *  Special 'where' statements, requiring complex references to other
 *  table, sub-queries etc. can be used, too, using 'setWhere' method
 *  that sets only the body of the 'where' section with any parentheses
 *  etc. The real column names & values gets filled in later using the
 *  normal methods for update/delete/select.
 *
 *  'where' column names may be preceeded by opening and closing 
 *  parentheses, and || to OR or && to AND the preceeded column to
 *  previous column. SoDam will add necessary number of closing parentheses
 *  at the end of the last column.
 *
 *  Example: array('(col1' => 1, 
 *                 ')((col2<>:col' => 'null',
 *                 ')||(col2' => 2) would result in
 *  ... (col1 = 1) AND ((col2 IS NOT null) OR (col2 = 2))
 *
 *  @var string $__Table Table name to modify
 *  @var string $__Backend Database backend name (PEAR style)
 *  @var string $__Date Date format used by the DB
 *  @var string $__Time Time format used by the DB
 *  @var string $__Datetime Datetime format used by the DB
 *  @var string $__WhereBody 'where' body.
 *  @var string $__Quote Quotation mark used with the backend
 *  @var array $__Options Various options
 *  @var integer $__Level Parentheses level
 */
class soDam
{
    var $__Tables;
    var $__Backend;
    var $__Date = 'Y-m-d';
    var $__Time = 'H:i:s';
    var $__DateTime = 'Y-m-d H:i:s';
    var $__WhereBody;
    var $__Quote;
    var $__Options = array();
    var $__Level = 0;


    /** Constructor
     *  @param string $Backend Database type in PEAR style.
     *  @param mixed $Tables Table name(s) to be modified: either an
     *    array or ';' delimited list of tablenames (and aliases)
     */
    function soDam($Backend, $Tables = '')
    {
        $this->__Backend = $Backend;
        $this->__setQuote($Backend);

        $options = array('pgsql' => array('trimming' => 1, 'incase' => 1));
        if ($opts = &$options[$Backend]) $this->setOptions($opts);
        if ($Tables) $this->setTables($Tables);
    } // soDam


    /** Sets generic options for database access.
     *  Valid options are: 'trimming' (bool) and 'incase' (bool).
     *  'trimming' will cause the usage of trim() function in search, and
     *  'incase' will cause search to be incase sensitive using lower().
     *  @param array $Options Associative array of key => value pairs.
     */
    function setOptions($Options) {
        $this->__Options = $Options;
    } // setOptions


    /** Determines the quotataion mark for backend
     *  @param string $Backend Database type in PEAR style.
     */
    function __setQuote($Backend)
    {
        switch ($Backend) {
            case 'pgsql':
                $this->__Quote = "'";
                break;
            default:
                $this->__Quote = '"';
        }; // switch
    } // __setQuote


    /** Sets tables and aliases for them.
     *  Example: $class->setTables('name alias');
     *  Example: $class->setTables('name1;name2');
     *  Example: $class->setTables('name1 alias1;name1 alias1');
     *  Example: $class->setTables(array('name' => 'alias'));
     *
     *  @param mixed $Tables When passing string, a list of tables
     *      may be passed as an ';' delimited list. Each table name may
     *      accompanied by alias name, delimited by space.
     *      When using associative array, each array key is the table
     *      name and the value the alias. Empty alias names gets replaced
     *      by actual table name.
     */
    function setTables($Tables)
    {
        $this->__Tables = array();

        if (is_array($Tables)) {
            $t_arr = &$Tables;
        } else {
            $t_arr = array();
            $arr = soExplode($Tables, ';');
            foreach ($arr as $table) {
                if (soStrPos($table, ' ') == -1) $table .= ' '.$table;
                list($name, $alias) = split(' ', $table);
                $t_arr[$name] = $alias;
            }; // foreach
        }; // if

        /* Set/add tables to $__Tables property */
        foreach ($t_arr as $table => $alias) {
            $this->__Tables[$alias] = $table;
        }; // foreach

    } // setTables

    
    /** Sets the date/time/timestand format used by the database
     *  Default values for these are ISO standard: yyyy-mm-yy and hh:mm:ss
     *  (where hh is in 24 hour notation).
     *  @param string $Type Date, time or datetime (timestamp) to set.
     *      'date' sets the date, 'time' the time and 'datetime' the
     *      datetime/timestamp format.
     *  @param string $Format Date/time format string in php style.
     */
    function setDateTime($Type, $Format)
    {
        switch ($Type) {
            case 'date':
                $this->__Date = $Format;
                break;
            case 'time':
                $this->__Time = $Format;
                break;
            case 'datetime':
                $this->__DateTime = $Format;
                break;
        }; // switch
    } // setDateTime

    
    /** Fixes the column name & value for SQL clauses
     *  Different datatype requires some modifications, like time is
     *  passed as Unix timestamp, but for database it needs to be formatted
     *  in a special way. Also, column name may be postfixed by :xxx notation
     *  to indicate the datatype that must be stripped. This function also
     *  adds the quotes, if necessary, to the value.
     *  All parameters are passed as referenced and modified.
     *  This function is only used internally by other class functions.
     *  @param string $ColName Column name with optional :datatype
     *  @param string $Value Value of the column.
     */
    function __fixNameTypeValue(&$ColName, &$Values) {
        if (!$is_arr = is_array($Values)) {
            $Values = array($Values);
        }; // if
        if (soStrPos($ColName, ':') == -1) {

            $types = array();
            /* No datatype? Try to guess */
            foreach ($Values as $key => $value) {            
                if (is_numeric($value)) {
                    $Values[$key] = floatval($value);
                    $type = 'num';
                } else {
                    $type = 'text';
                }; // else if num test

                if (!in_array($type, $types)) $types[] = $type;

                if (count($types) > 1) {
                    $types[0] = 'text';
                    break;
                };
            };
            $ColName .= ':'.$types[0];
        }; // if

        list($ColName, $type) = split(':', $ColName);
        
        if (soStrPos($ColName, '#') != -1) {
            list($ColName, $dummy) = split('#', $ColName);
        }; // if

        /* Following types needs formatting */
        foreach ($Values as $key => $value) {
            switch ($type) {
                case 'time':
                    $Values[$key] = date($this->__Time, $value);
                    break;
                case 'date':
                    $Values[$key] = date($this->__Date, $value);
                    break;
                case 'datetime':
                case 'timestamp':
                    $Values[$key] = date($this->__DateTime, $value);
                    break;
            }; // switch

            /* To quote or not to quote? That is the question. */
            if (in_array($type, array('text', 'date', 'time', 'datetime'))) {
                $Values[$key] = $this->__Quote.addslashes($value).$this->__Quote;
            }; // if
        }; // foreach

        if (!$is_arr) $Values = $Values[0];

        return $type;
    } // __fixNameTypeValue


    /** Updates a row/rows in a table
     *  Example: update(array('datacol1' => $value1,
     *                        'datacol2:date' => $value),
     *                  array('wherecol1' => $value3,
     *                        'wherecol2' => $value4));
     *  @param array $DataCols Data columns: array('colname' => $value)
     *  @param array $WhereCols Where columns: array('colname' => $value).
     *  @return string SQL clause for update
     */
    function getUpdate($DataCols, $WhereCols = array())
    {
        /* Prepare data cols */
        $dcols = array();
        foreach ($DataCols as $name => $value) {
            $this->__fixNameTypeValue($name, $value);
            $dcols[] = $name.' = '.$value;
        }; // foreach

        $table = $this->__getTableNameBy(0);
        if (count($WhereCols)) $this->__setWhere($WhereCols);
        $result = 'UPDATE '.$table.' SET '.implode(', ', $dcols).' '.
                  'WHERE '.$this->__WhereBody.';';
        return $result;
    } // getUpdate

    
    /** Deletes a row/rows in a table
     *  Example: delete(array('datacol2:date' => $value),
     *                  array('wherecol2' => $value4));
     *  @param array $WhereCols Where columns: array('colname' => $value).
     *  @return string SQL clause for update
     */
    function getDelete($WhereCols = array())
    {

        $table = $this->__getTableNameBy(0);
        if (count($WhereCols)) $this->__setWhere($WhereCols);
        $result = 'DELETE FROM '.$table.' '.
                  'WHERE '.$this->__WhereBody.';';
        return $result;
    } // getDelete

    
    function __getTableNameBy($Key)
    {
        /* Get the table name, the 1st table */
        $keys = array_keys($this->__Tables);

        if (is_numeric($Key)) $Key = $keys[$Key];
        return $this->__Tables[$Key];
    } // __getTableNameBy
    
    
    /** Returns INSERT clause.
     *  Example: insertRow(array('col1', 'col2'), array($a, $b));
     *  @param array $Cols Data columns: array('colname' => $value)
     *  @return string SQL clause for insert
     */
    function getInsert($Cols)
    {
        $cols = array();
        $vals = array();
    
        foreach ($Cols as $name => $value) {
            $this->__fixNameTypeValue($name, $value);
            $cols[] = $name;
            $vals[] = $value;
        }; // foreach
    
        $result = 'INSERT INTO '.
                   $this->__getTableNameBy(0).' ('.implode(', ', $cols).
                ') VALUES ('.implode(', ', $vals).');';
    
        return $result;
    } // getInsert


    /** Returns SELECT clause.
     *  @param array $DataCols Data columns: array('col1', 'col2') OR
     *  array('alias1' => 'col1', 'alias2' => 'col2')
     *  @param array $WhereCols Where columns: array('colname' => $value).
     *  @param array $Options Optional parameters: 'limit', 'offset' and
     *  'distinct' are valid.
     *  @return string SQL clause for update
     */
    function getSelect($DataCols, $WhereCols = array(),
                       $OrderCols = array(), $Options = array())
    {
        
        /* Prepare order cols */
        $scols = array();
        foreach ($OrderCols as $name => $value) {
            $scols[] = $name.' '.$value;
        }; // foreach

        foreach (array('distinct', 'limit', 'offset') as $key) {
            if (!isset($Options[$key])) $Options[$key] = 0;
        }; // forerach

        $table = $this->__getTableNameBy(0);
        if (count($WhereCols)) {
            $this->__setWhere($WhereCols);
        } else {
            $this->setWhere('');
        };
        $result = 'SELECT ';
        if ($Options['distinct']) $result .= 'DISTINCT ';
        $data_cols = $this->__getDataCols($DataCols);
        $result .= implode(', ', $data_cols).' '.
                  'FROM '.$this->__getTables().' ';
        if ($this->__WhereBody) $result .= 'WHERE '.$this->__WhereBody.' ';
        if (count($scols)) $result .= 'ORDER BY '.implode(', ', $scols).' ';
        if ($value = &$Options['limit']) $result .= 'LIMIT '.$value;
        if ($value = &$Options['offset']) $result .= 'OFFSET '.$value;

        return $result. ';';
    } // getSelect


    function __getDataCols($Cols) {
        $result = array();
        foreach ($Cols as $key => $value) {
            $col = $value;
            if (!is_int($key)) $col .= ' AS '.$key;
            $result[] = $col;
        }; // foreach

        return $result;
    } // __getDataCols


    /** Returns tables with aliases as a comma delimited string
     *  @return string Tables, comma delimited list
     */
    function __getTables()
    {
        $result = array();
        foreach ($this->__Tables as $alias => $name) {
            $table = $name;
            if ($alias != $name) $table .= ' '.$alias;
            $result[] = $table;
        }; // foreach

        return implode(', ', $result);
    } // __getTables


    /** Populates 'where' body with values
     *  Example: populateWhere(array('col1' => $value));
     *  @param array $Cols Cols with vals: array('colname' => $value)
     */
    function populateWhere($Cols)
    {
        foreach ($Cols as $name => $value) {
            $this->__fixNameTypeValue($name, $value);
            $this->__WhereBody = 
                str_replace('$'.$name, $value, $this->__WhereBody);
        }; // foreach
        
    } // populateWhere    


    function __splitPrefix(&$ColName)
    {
        $prefix = '';
        while ($ColName &&
               in_array(($chr = substr($ColName, 0, 1)),
                         array(')', '(', '|', '&'))) {
            $prefix .= $chr;
            $ColName = substr($ColName, 1);
        }; // while

        $close = substr_count($prefix, ')');
        $open = substr_count($prefix, '(');
        $this->__Level += $open -$close;
        $result = array('close' => 
                            str_repeat(')', $close),
                        'open' => 
                            str_repeat('(', $open),
                        'or' => 
                            (soStrPos($prefix, '||') != -1));
        return $result;
    } // __splitPrefix


    /** Sets 'where' for simple cases
     *  @param array $Cols An associated col => value pairs of where cols.
     *  @return string Where clause
     */
    function __setWhere(&$Cols)
    {

        $result = '';
        foreach ($Cols as $name => $value) {
            
            /* Sort out the OR & AND glue */
            $prefix = $this->__splitPrefix($name);

            if ($result) {
                $glue = $prefix['close'];
                ($prefix['or']) ? $glue .= ' OR ' : $glue .= ' AND ';
                $glue .= $prefix['open'];
                $result .= $glue;
            } else {
                $result = $prefix['open'];
            }; // if
            $type = $this->__fixNameTypeValue($name, $value);

            $funcs = array('prefix' => '', 'suffix' => '');
            if ($type == 'text') {
                if ($this->__Options['incase']) {
                    $funcs['prefix'] .= 'lower(';
                    $funcs['suffix'] .= ')';
                }; // if
                if ($this->__Options['trimming']) {
                    $funcs['prefix'] .= 'trim(';
                    $funcs['suffix'] .= ')';
                }; // if
            }; // if
            $op = $this->__getOperator($name, $value);
            if (in_array(trim($op), array('IN', 'NOT IN')) && is_array($value)) {
                $value = '('.implode(', ', $value).')';
            };
            $result .= $funcs['prefix'].$name.$funcs['suffix'].$op.
                       $funcs['prefix'].$value.$funcs['suffix'];
        }; // foreach

        $result .= str_repeat(')', $this->__Level);
        $this->setWhere($result);
    } // __setWhere


    /** Gets operator for col - value comparison.
     *  Column name might be suffixed with operator to indicate other than
     *  "is equal to" comparison of column and it's value. The valid
     *  operators are:
     *  '==' equal to (or IS for null testing)
     *  '!=' || '<>' not equal to (or IS NOT for null testing)
     *  '<=' less than or equal to
     *  '>=' greater than or equal to
     *  '<<' less than
     *  '>>' greater than
     *  '=~' like
     *  '!~' not like
     *  '{}' in +++
     *  '}{' not in +++
     *  N.B. The two character notation for every operation is intentional
     *  to maximise the speed of checking the operator.
     *
     *  +++ These columns will also accept array of values as input
     *
     *  @param $ColName Column name to get operator from/for
     *  @return string Operator for column
     */
    function __getOperator(&$ColName, &$Value)
    {
        $operators = array('=' => array('=='),
                           '<>' => array('<>', '!='),
                           '<=' => array('<='),
                           '>=' => array('>='),
                           '<' => array('<<'),
                           '>' => array('>>'),
                           'LIKE' => array('=~'),
                           'NOT LIKE' => array('!~'),
                           'IN' => array('{}'),
                           'NOT IN' => array('}{'));
        $end = &substr($ColName, -2);

        // v-- default operator is 'equal to'
        $result = '=';
        foreach ($operators as $op => $list) {
            if (in_array($end, $list)) {
                $ColName = substr($ColName, 0, strlen($ColName) -2);
                $result = $op;
                break;  // <-- BREAK
            }; // if
        }; // 
        if (strtoupper($Value) == 'NULL') {
            $null_ops = array('=' => 'IS', '<>' => 'IS NOT');
            $result = $null_ops[$result];
        }; // if
        
            
        return ' '.$result.' ';
    } // __getOperator

    
    /** Sets the 'where' body.
     *  @param string $WhereBody Where body (without 'WHERE')
     */
    function setWhere($WhereBody)
    {
        $this->__WhereBody = $WhereBody;
    } // setWhere

    
}; // soDam


class soDax extends soDam {
    var $__DbObj;
    var $__RowCount;


    /** Constructor
     *  @param string $Backend Database object
     *  @param mixed $Tables Table name(s) to be modified: either an
     *    array or ';' delimited list of tablenames (and aliases)
     */
    function soDax($Backend, $Tables = '')
    {
        $this->soDam($Backend->phptype, $Tables);
        $this->__DbObj = $Backend;
    } // soDax


    /** Returns a pointer to PEAR DB object
     *  May be used to check for DB errors: DB::isError($dax->getDbObj)
     *  @return object Pointer to DB object.
     */
    function &getDBObj()
    {
        return $this->__DbObj;
    } // getDBObj


    /** Runs SELECT clause.
     *  @param array $DataCols Data columns: array('col1', 'col2')
     *  @param array $WhereCols Where columns: array('colname' => $value).
     *  @param array $Distinct When set, adds runs SELECT DISTINCT
     *  @param array $Limit Optional LIMIT value
     *  @param array $Offset Optional OFFSET value
     *  @return object DB query result object.
     */
    function select($DataCols, $WhereCols = array(),
                    $OrderCols = array(), $Options = array())
    
    {
        $sql = $this->getSelect($DataCols, $WhereCols, $OrderCols, $Options);
        soDebug('soDax->select: '.$sql, SOD_DET);
        $result = $this->__DbObj->query($sql);

        if (DB::isError($result)) {
            $this->__RowCount = -1;
        } else {
            $this->__RowCount = $result->numRows();
        }; // else if

        return $result;
    } // select


    /** Returns no. rows found by the last 'select'
     *  @return No. rows found or -1 when last 'select' failed.
     */
    function getRowCount()
    {
        return $this->__RowCount;
    } // getRowCount


    /** Like "select", but returns no. rows found instead of rows
     *  This is a simple hasRow(s) method.
     *  @param array $WhereCols Where columns: array('colname' => $value).
     *  @return integer No. of rows found or -1 on error.
     */
    function count($WhereCols = array())
    {
        $sql = $this->getSelect(array('count(*)'), $WhereCols);

        soDebug('soDax->count: '.$sql, SOD_LO);
        $qresult = $this->__DbObj->query($sql);

        if (DB::isError($qresult)) {
            $result = -1;
        } else {
            $row = $qresult->fetchRow();
            $result = $row[0];
        }; // else if

        return $result;
    } // select


    /** Runs UPDATE clause.
     *  Updates given (data) columns.
     *  @param array $DataCols Data columns to update:
     *      array('col1' => $value1, 'col2' => $value2, ...) 
     *  @param array $WhereCols Where columns: array('colname' => $value).
     *  @return object DB query result object.
     */
    function update($DataCols, $WhereCols = array())
    {
        $sql = $this->getUpdate($DataCols, $WhereCols);
        soDebug('soDax->update: '.$sql, SOD_LO);
        return $this->__DbObj->query($sql);
    } // update


    /** Runs INSERT clause.
     *  Inserts given (data) columns.
     *  @param array $DataCols Data columns to update: 
     *      array('col1' => $value1, 'col2' => $value2, ...) 
     *  @return object DB query result object.
     */
    function insert($DataCols)
    {
        $sql = $this->getInsert($DataCols);
        soDebug('soDax->insert: '.$sql, SOD_LO);
        return $this->__DbObj->query($sql);
    } // insert


    /** Runs DELETE clause.
     *  Deletes specified rows defined by where cols.
     *  @param array $WhereCols Where columns: array('colname' => $value).
     *  @return object DB query result object.
     */
    function delete($WhereCols = array())
    {
        $sql = $this->getDelete($WhereCols);
        soDebug('soDax->delete: '.$sql, SOD_LO);
        return $this->__DbObj->query($sql);
    } // delete


}; // soDax

/*
# TEST BLOCK

$sql = new soDam('pgsql', array('footable' => 'f', 'bartable' => 'b'));
#echo $sql->getSelect(array('foo'),
#                     array('(foo:text' => 'null', ')(||bar<>:col' => 'null'));
$peardbobj = DB::connect('pgsql://postgres@localhost/test');
$exe = new soDax($peardbobj, array('alb10' => 'alias_b', 'aut10' => 'alias_a'));
$exe->setTables(array('a' => 'aut10', 'b' => 'alb10'));
echo $exe->getSelect(array('aname' => 'a.name'),
                     array('b.authorid' => 10, 
                           'a.authorid:col' => 'b.authorid'));

#$sql->setWhere('((f.foo = $foo) or (b.bar = $bar) and (b.baa = $baa))');
#$sql->populateWhere(array('foo' => 'oof', 'bar:text' => '3',
#                    'baa' => 'aab'));
#$result = $sql->delete(array('col1' => 'new', 'col2:date' => time()));
#$result = $sql->insert(array('col1' => 'new', 'col2:date' => time()));
#$result = $sql->update(array('col1' => 'new'));
#$result = $sql->select(array('col1'));
#$result = $sql->select(array('col1'), array('col2:text' => 3), 
#                       array('col2' => ''));

echo $result."\n";

*/

?>
